package org.zwy.gradetrack.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.zwy.gradetrack.entity.CourseScore;
import org.zwy.gradetrack.vo.CourseScoreVo;
import java.math.BigDecimal;

import java.util.List;

/**
 * 课程得分Mapper
 */
@Mapper
public interface CourseScoreMapper extends BaseMapper<CourseScore> {
    
    /**
     * 获取学生的课程成绩列表（包含课程和毕业生信息）
     */
    @Select("<script>" +
            "SELECT cs.*, " +
            "c.course_code as courseCode, " +
            "c.course_name as courseName, " +
            "c.course_type as courseType, " +
            "c.credit, " +
            "g.name as graduateName, " +
            "g.student_id as studentId " +
            "FROM course_score cs " +
            "LEFT JOIN course c ON cs.course_id = c.id " +
            "LEFT JOIN graduate g ON cs.graduate_id = g.id " +
            "<where>" +
            "1=1" +
            "<if test=\"graduateId != null\"> AND cs.graduate_id = #{graduateId}</if>" +
            "<if test=\"courseCode != null and courseCode != ''\"> AND c.course_code LIKE CONCAT('%', #{courseCode}, '%')</if>" +
            "<if test=\"courseName != null and courseName != ''\"> AND c.course_name LIKE CONCAT('%', #{courseName}, '%')</if>" +
            "<if test=\"courseType != null and courseType != ''\"> AND c.course_type = #{courseType}</if>" +
            "</where>" +
            "</script>")
    List<CourseScoreVo> getStudentCourseScoresWithDetails(@Param("graduateId") Long graduateId, @Param("courseCode") String courseCode, @Param("courseName") String courseName, @Param("courseType") String courseType);

    @Select("SELECT COUNT(DISTINCT graduate_id) FROM course_score WHERE course_id = #{courseId}")
    Integer countDistinctStudentsByCourse(Integer courseId);
    
    @Select("SELECT AVG(score) FROM course_score WHERE course_id = #{courseId}")
    BigDecimal calculateAverageScore(Integer courseId);
    
    @Select("SELECT COUNT(*) FROM course_score WHERE course_id = #{courseId} AND score >= 60")
    Integer countPassingStudents(Integer courseId);
} 